/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package fr.p10.miage.gestionnote.modelservice;

import fr.p10.miage.gestionabsence.model.Connection;
import fr.p10.miage.gestionnote.model.EcEtudiant;
import fr.p10.miage.gestionnote.model.EcIntervenant;
import fr.p10.miage.gestionnote.model.Etudiant;
import java.nio.charset.Charset;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

/**
 *
 * @author gensky
 */
public class EcsIntervenantServices extends UtilService
{
    private static Connection cx;

    public static List<EcIntervenant> getEcsIntervenant(int idItervenant) throws SQLException 
    {
        List<EcIntervenant> listeEcsIntervenant = new ArrayList<EcIntervenant>();

        Calendar now = Calendar.getInstance();   
        int annee=now.get(Calendar.YEAR);  
        
        String sql ="select LEC.idec,\n" +
                    "P.IDP,\n" +
                    "F.IDF,\n" +
                    "UE.IDUE,\n" +
                    "LEC.nom,\n" +
                    "LEC.code,\n" +
                    "UE.COEFF,\n" +
                    "UE.NBRTD,\n" +
                    "UE.NBRCM, \n" +
                    "F.filiere, \n" +
                    "F.parcours, \n" +
                    "F.grade, \n" +
                    "F.Niveau, \n" +
                    "F.typee, \n" +
                    "P.annee\n" +
                    "from INTERVENANT INTV, INTERV_EC INTEC, EC LEC, EC_UE UE, Formation F, Promotion P\n" +
                    "Where INTEC.idi=intv.idi\n" +
                    "AND lec.idec=intec.idec\n" +
                    "AND lec.idec=ue.idec\n" +
                    "AND F.idf=UE.idf\n" +
                    "AND p.idf=F.idf\n" +
                    "AND (p.annee="+annee+" OR p.annee="+(annee+1)+" OR p.annee="+(annee-1)+")\n" +
                    "AND INTV.idi= "+idItervenant+" \n" +
                    "ORDER BY P.annee DESC,F.grade DESC,F.niveau DESC, F.parcours DESC,F.typee DESC ";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                
                listeEcsIntervenant.add(new EcIntervenant(
                        resultat.getInt("idec"),
                        resultat.getInt("idf"),
                        resultat.getInt("idp"),
                        resultat.getInt("idue"),
                        resultat.getString("nom"),
                        resultat.getString("code"),
                        resultat.getFloat("coeff"),
                        resultat.getInt("nbrtd"),
                        resultat.getInt("nbrcm"),
                        idItervenant,
                        resultat.getInt("annee"),
                        resultat.getString("grade"),
                        resultat.getString("niveau"),
                        resultat.getString("filiere"),
                        resultat.getString("parcours"),
                        resultat.getString("typee")
                        )
                       );
                
            }
            resultat.close();
        }

        pst.close();
        return listeEcsIntervenant;
    }

    public static List<EcEtudiant> getEtudiant(int idec,int annee) throws SQLException 
    {
        List<EcEtudiant> listeEtudiants = new ArrayList<EcEtudiant>();

        String sql ="select LEC.idec,LEC.nom ,P.annee, E.id, E.nom AS nom_etudiant ,E.prenom,E.mail\n" +
                    "from INTERVENANT INTV, INTERV_EC INTEC, EC LEC, EC_UE UE, Formation F, Promotion P, ETUDIANT_PROMOTION EP, etudiant E\n" +
                    "Where INTEC.idi=intv.idi\n" +
                    "AND lec.idec=intec.idec\n" +
                    "AND lec.idec=ue.idec\n" +
                    "AND F.idf=UE.idf\n" +
                    "AND p.idf=F.idf\n" +
                    "AND ep.idp=p.idp\n" +
                    "AND ep.ide=e.id\n" +
                    "AND lec.idec="+idec+"\n"+
                    "AND p.annee="+annee+"";
                    

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            while (resultat.next()) {
                listeEtudiants.add(new EcEtudiant(
                        resultat.getInt("id"),
                        resultat.getString("nom_etudiant"),
                        resultat.getString("prenom"),
                        resultat.getString("mail")
                        )
                       );
                
            }
            resultat.close();
        }

        pst.close();
        return listeEtudiants;
    }
    
    public static float calculMoyenneEcByEtudiantId(int idEtudiant, int idec, int idp) throws SQLException, Exception
    {
        float moy = 0;
        String sql = "SELECT ROUND(SUM(((n.note*20/c.echelle)*c.coefficient))/SUM(c.coefficient),2) AS note\n" +
                    "FROM note n, controle c, ec \n" +
                    "WHERE n.idcontrole = c.idcontrole\n" +
                    "AND n.dispense=0\n" +
                    "AND c.idec = ec.idec\n" +
                    "AND n.idetudiant ="+idEtudiant+"\n" +
                    "AND c.idp ="+idp+"\n" +
                    "AND c.idec = "+idec+"";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                    moy = resultat.getFloat("note");
            } else {
                throw new Exception("Pas un float retournée");
            }
            resultat.close();
        }
        pst.close();
        return moy;

    }

    
    public static String getCommentaireByEtudiantId(int idec, int idEtudiant,int idPromotion) throws SQLException, Exception
    {
//        System.out.println("idec "+idec);
//        System.out.println("idp "+idPromotion);
//        System.out.println("idEtudiant "+idEtudiant);
        
        String commentaire ="-";
        
        String sql = "SELECT ece.commentaire\n" +
                    "FROM ec, etudiant e, promotion p, ec_commentaire_etudiant ece\n" +
                    "WHERE ece.idetudiant = e.id\n" +
                    "AND ece.idec = ec.idec\n" +
                    "AND ece.idpromotion = p.idp\n" +
                    "AND ece.idetudiant = "+idEtudiant+"\n" +
                    "AND ece.idec = "+idec+"\n" +
                    "AND ece.idpromotion = "+idPromotion+"";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql);

        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (!resultat.next())
            {
                
            }else {
//                System.out.println("ds Requete "+commentaire);
                if(!resultat.getString("commentaire").equals("") || resultat.getString("commentaire") != null)
                    commentaire = resultat.getString("commentaire");
            }
            resultat.close();
        }
        pst.close();
//        System.out.println("ds services "+commentaire);
        return commentaire;
        
    }
    
    public static void ajouterCommentaireEtudiant(int idec, int idEtudiant, int idp, String commentaire) throws SQLException, Exception 
    {
        int count = 0;
        String sql = "SELECT Count(*) as result\n" +
                    "FROM ec_commentaire_etudiant ece\n" +
                    "WHERE ece.idetudiant = "+idEtudiant+"\n" +
                    "AND ece.idec = "+idec+"\n" +
                    "AND ece.idpromotion = "+idp+"";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                if (resultat.getInt("result") != 0) {
                    count = resultat.getInt("result");
                }
            } else {
                throw new Exception("Pas un int retourné");
            }
            resultat.close();
        }
        pst.close();
        
//        commentaire=new String(commentaire.getBytes("UTF8"), "ISO-8859-15");
        
        if(count==1)
        {
               String insertTableSQL = "UPDATE EC_COMMENTAIRE_ETUDIANT SET commentaire=? WHERE idec=? AND idetudiant=? AND idpromotion=?";
                PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
                preparedStatement.setString(1,commentaire);
                preparedStatement.setInt(2,idec);
                preparedStatement.setInt(3,idEtudiant);
                preparedStatement.setInt(4,idp);
                preparedStatement.executeUpdate();
                preparedStatement.close();


                preparedStatement.close();
        }
        else
        {
            try {
            String insertTableSQL = "INSERT INTO EC_COMMENTAIRE_ETUDIANT (idec,idetudiant,idpromotion,commentaire) VALUES(?,?,?,?)";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
            preparedStatement.setInt(1, idec);
            preparedStatement.setInt(2, idEtudiant);
            preparedStatement.setInt(3, idp);
            preparedStatement.setString(4, commentaire);
            preparedStatement.executeUpdate();

            } catch (SQLException s) {
                System.out.println("SQL statement add note is not executed!"+s);
            }
        }
        
    }
    
    public static void publierEc(int idEc, int idF,int idP) throws SQLException, Exception 
    {
        try {
            String insertTableSQL = "INSERT INTO EC_PUBLIER (idec,idf,idp,publier) VALUES(?,?,?,?)";
            PreparedStatement preparedStatement = cx.getBDD().prepareStatement(insertTableSQL);
            preparedStatement.setInt(1, idEc);
            preparedStatement.setInt(2, idF);
            preparedStatement.setInt(3, idP);
            preparedStatement.setInt(4, 1);
            preparedStatement.executeUpdate();

            } catch (SQLException s) {
                System.out.println("SQL statement add note is not executed!"+s);
            }
    }

    public static int calculNbControlesPublier(int idec, int idp) throws SQLException, Exception 
    {
        
         int nbControlePublier = 0;

        String sql = "SELECT Count(c.idcontrole) AS result\n" +
                    "FROM promotion p, ec, controle c\n" +
                    "WHERE c.idp = p.idp\n" +
                    "AND c.idec = ec.idec\n" +
                    "AND c.idp = "+idp+"\n" +
                    "AND c.idec = "+idec+"\n" +
                    "ANd c.publier = 1";

        PreparedStatement pst = cx.getBDD().prepareStatement(sql, ResultSet.TYPE_SCROLL_INSENSITIVE,
                ResultSet.CONCUR_READ_ONLY);
        if (pst.execute()) {
            ResultSet resultat = pst.getResultSet();
            if (resultat.first() == true) {
                    nbControlePublier = resultat.getInt("result");
            } else {
                throw new Exception("Pas un int retourné");
            }
            resultat.close();
        }
        pst.close();
        return nbControlePublier;
    }
}
